package dbms;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import utility.Utility;

public class DBAddingFunctions {
	
	public static boolean addUser(String userId, String password)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "INSERT INTO " + DBTables.UTENTE + " VALUES (?, ?)";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, password);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean removeUser(String userId)
	{
		// TODO: DEVO RIMUOVERE TUTTI I PROGRAMMI ASSOCIATI ??
		
		try{
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "DELETE FROM " + DBTables.UTENTE + " WHERE userid=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, userId);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean setUserPassword(String userId, String password)
	{
		try{
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "UPDATE " + DBTables.UTENTE + " SET password=? WHERE userid=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, password);
			ps.setString(2, userId);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean addProgram(String programName, String extenzion)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "INSERT INTO " + DBTables.PROGRAM + " (name, extenzion, new_file) VALUES (?, ?, 'YES')";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, programName);
			ps.setString(2, extenzion);
			ps.executeUpdate();
						
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			System.out.println("PROGRAM ADDED: " + programName + " - " + extenzion);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean removeProgram(String programName)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "DELETE FROM " + DBTables.PROGRAM + " WHERE name = ?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, programName);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean addProgramToUser(String userId, String programId)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "INSERT INTO " + DBTables.PROG_UTENTE + " (utente, program) VALUES (?, ?)";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, programId);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}
	
	public static boolean removeProgramToUser(String userId, String programId)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "DELETE FROM " + DBTables.PROG_UTENTE + " WHERE utente=? AND program=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, programId);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}
	
	public static boolean removeProgramToUser(int relationId)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "DELETE FROM " + DBTables.PROG_UTENTE + " WHERE id = ?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setInt(1, relationId);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}
	
	public static boolean addNewFile(String fileName, String programName)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "INSERT INTO " + DBTables.FILE + " (name, program, last_edit_by, last_edit_when, release_number) VALUES (?, ?, ?, ?, ?)";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, fileName);
			ps.setString(2, programName);
			ps.setString(3, "None");
			ps.setTimestamp(4, Utility.getTimestamp());
			ps.setInt(5, 0);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean removeFile(String fileName, String programName)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "DELETE FROM " + DBTables.FILE + " WHERE name = ? AND program = ?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, fileName);
			ps.setString(2, programName);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean removeFile(int fileId)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "DELETE FROM " + DBTables.FILE + " WHERE id = ?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setInt(1, fileId);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean addRelease(int fileId, String userId, int releaseNumber, String releaseNote)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "INSERT INTO " + DBTables.RELEASE + " (file, added_by, added_when, number, note) VALUES (?, ?, ?, ?, ?)";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setInt(1, fileId);
			ps.setString(2, userId);
			ps.setTimestamp(3, Utility.getTimestamp());
			ps.setInt(4, releaseNumber);
			ps.setString(5, releaseNote);
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}
	
	public static boolean setFileLastRelease(int fileId, int lastRelease)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "UPDATE " + DBTables.FILE + " SET release_number=? WHERE id=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setInt(1, lastRelease);
			ps.setInt(2, fileId);

			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}
	
	public static boolean setFileLastCommitter(int fileId, String userId)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "UPDATE " + DBTables.FILE + " SET last_edit_by=? WHERE id=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setInt(2, fileId);

			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}

	public static boolean changeProgramName(String programName, String newName) {
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "UPDATE " + DBTables.PROGRAM + " SET name=? WHERE name=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, newName);
			ps.setString(2, programName);
			
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean setProgramParameter(String programName, String parameter, Object value)
	{
		try {
			DBConnection.CONNECTION.setAutoCommit(false);

			String sql = "UPDATE " + DBTables.PROGRAM + " SET " + parameter + "='" + value + "' WHERE name=?";
			PreparedStatement ps = DBConnection.CONNECTION.prepareStatement(sql);
			ps.setString(1, programName);
			
			ps.executeUpdate();
			
			DBConnection.CONNECTION.commit();
			DBConnection.CONNECTION.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}		
	}

}